package com.donleo.sharding;

import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory;
import org.apache.shardingsphere.infra.config.algorithm.AlgorithmConfiguration;
import org.apache.shardingsphere.infra.config.props.ConfigurationPropertyKey;
import org.apache.shardingsphere.infra.hint.HintManager;
import org.apache.shardingsphere.sharding.api.config.ShardingRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.rule.ShardingTableRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.strategy.sharding.HintShardingStrategyConfiguration;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

/**
 * 测试 ShardingSphere 数据分片
 * 主库：127.0.0.1
 * 从库：192.168.50.202
 * <p>
 */
@Slf4j
public class ShardingPlanDataTest {

    private static DataSource ds0 = DataSourceUtil.createDataSource("127.0.0.1", "root", "123456", "test_split1");

    private static final String GENERAL_DATABASE_HIT = "GENERAL-DATABASE-HIT";

    private static final String GENERAL_TABLE_HIT = "GENERAL-TABLE-HIT";

    public static void main(String[] args) throws SQLException {


        Map<String, DataSource> map = new HashMap<>();
        map.put("ds0",ds0);
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getShardingAlgorithms().put(GENERAL_DATABASE_HIT,new AlgorithmConfiguration(GENERAL_DATABASE_HIT,null));
        shardingRuleConfig.getShardingAlgorithms().put(GENERAL_TABLE_HIT,new AlgorithmConfiguration(GENERAL_TABLE_HIT,null));
        shardingRuleConfig.setDefaultDatabaseShardingStrategy(new HintShardingStrategyConfiguration(GENERAL_DATABASE_HIT));

        // TODO 增加规则
        ShardingTableRuleConfiguration jlAdvertiserDailyData = new ShardingTableRuleConfiguration("jl_advertiser_daily_data", "ds$->{0..0}.jl_advertiser_daily_data_$->{2021..2022}");
        jlAdvertiserDailyData.setTableShardingStrategy(new HintShardingStrategyConfiguration(GENERAL_TABLE_HIT));

        // 配置分片规则
        shardingRuleConfig.getTables().add(jlAdvertiserDailyData);


        // 上下文
        HintManager hintManager = HintManager.getInstance();
        hintManager.addTableShardingValue("jl_advertiser_daily_data", "2022");


        Properties properties = new Properties();
        properties.setProperty(ConfigurationPropertyKey.KERNEL_EXECUTOR_SIZE.getKey(), String.valueOf(10));
        // 获取数据源对象
        DataSource dataSource = ShardingSphereDataSourceFactory.createDataSource(map, Collections.singleton(shardingRuleConfig), properties);
        Connection connection = dataSource.getConnection();
        Statement statement = connection.createStatement();
        long start =System.currentTimeMillis();

        //statement.executeQuery("SELECT cost as all1  FROM jl_advertiser_daily_data  limit 10000,10");
        //statement.executeQuery("SELECT sum(cost) as all1 FROM jl_advertiser_daily_data where stat_day between '2021-12-01' and '2022-01-31' limit 10000,10");
//        statement.executeQuery("select" +
//                " m.advertiser_id as advertiserId," +
//                " SUM(cost) as cost1," +
//                " convert(SUM(cost),decimal(12,2)) as cost," +
//                " convert(SUM(cost) / SUM(`show`)*1000,decimal(12,2)) as avgShowCost" +
//                "  from" +
//                " jl_auth_account_advertiser m" +
//                " LEFT JOIN jl_advertiser_daily_data c ON c.advertiser_id = m.advertiser_id  and stat_day BETWEEN '2021-03-01' and '2022-12-31'" +
//                " where c.cost>0" +
//                "  group by m.advertiser_id" +
//                "  order by m.advertiser_id" +
//                "  LIMIT 100,20");



//        statement.executeQuery(" select\n" +
//                "    count(*) as cost2 \n" +
//                "    from\n" +
//                "   (\n" +
//                "    SELECT\n" +
//                "    sum(cost) as cost1\n" +
//                "    FROM\n" +
//                "    jl_advertiser_daily_data c \n" +
//                "    where c.cost>0 and c.stat_day BETWEEN  '2022-03-01' and '2022-12-31'\n" +
//                "    ) a");


          // statement.executeQuery("SELECT count(*) FROM ( SELECT cost FROM jl_advertiser_daily_data ) as s");
        // statement.executeQuery("SELECT * FROM jl_advertiser_daily_data o");

        //statement.execute("INSERT INTO `jl_advertiser_daily_data` (`advertiser_id`, `stat_datetime`, `stat_day`, `ac`, `active`, `active_cost`, `active_pay_cost`, `active_pay_rate`, `active_rate`, `active_register_cost`, `active_register_rate`, `ad_tag`, `advanced_creative_counsel_click`, `advanced_creative_coupon_addition`, `advanced_creative_form_click`, `advanced_creative_form_submit`, `advanced_creative_phone_click`, `age`, `approval_count`, `attribution_active_pay7dper_count`, `attribution_convert`, `attribution_convert_cost`, `attribution_deep_convert`, `attribution_deep_convert_cost`, `attribution_game_pay7dcost`, `attribution_game_pay7dcount`, `attribution_next_day_open_cnt`, `attribution_next_day_open_cost`, `attribution_next_day_open_rate`, `attribution_wechat_first_pay30dcost`, `attribution_wechat_first_pay30dcount`, `attribution_wechat_first_pay30drate`, `attribution_wechat_login30dcost`, `attribution_wechat_login30dcount`, `attribution_wechat_pay30damount`, `attribution_wechat_pay30droi`, `average_play_time_per_play`, `avg_click_cost`, `avg_show_cost`, `button`, `card_show`, `city_name`, `click`, `click_call_dy`, `click_download`, `click_install`, `click_landing_page`, `click_shopwindow`, `click_website`, `comment`, `commute_first_pay_count`, `consult`, `consult_effective`, `convert`, `customer_effective`, `convert_cost`, `convert_rate`, `cost`, `coupon`, `coupon_single_page`, `creative_material_mode`, `ctr`, `deep_convert`, `deep_convert_cost`, `deep_convert_rate`, `download`, `download_finish`, `download_finish_cost`, `download_finish_rate`, `download_start`, `download_start_cost`, `download_start_rate`, `first_order_count`, `first_rental_order_count`, `follow`, `form`, `game_addiction`, `game_addiction_cost`, `game_addiction_rate`, `game_pay_cost`, `game_pay_count`, `gender`, `home_visited`, `ies_challenge_click`, `ies_music_click`, `image_mode`, `in_app_cart`, `in_app_detail_uv`, `in_app_order`, `in_app_pay`, `in_app_uv`, `install_finish`, `install_finish_cost`, `install_finish_rate`, `inventory`, `landing_type`, `like`, `live_fans_club_join_cnt`, `live_watch_one_minute_count`, `loan_completion`, `loan_completion_cost`, `loan_completion_rate`, `loan_credit`, `loan_credit_cost`, `loan_credit_rate`, `location_click`, `lottery`, `luban_live_click_product_cnt`, `luban_live_comment_cnt`, `luban_live_enter_cnt`, `luban_live_follow_cnt`, `luban_live_gift_amount`, `luban_live_gift_cnt`, `luban_live_pay_order_count`, `luban_live_pay_order_stat_cost`, `luban_live_share_cnt`, `luban_live_slidecart_click_cnt`, `luban_order_cnt`, `luban_order_roi`, `luban_order_stat_amount`, `map_search`, `message`, `message_action`, `pay_count`, `phone`, `phone_confirm`, `phone_connect`, `phone_effective`, `platform`, `play100feed_break`, `play25feed_break`, `play50feed_break`, `play75feed_break`, `play_duration_sum`, `play_over_rate`, `poi_address_click`, `poi_collect`, `pre_loan_credit`, `pre_loan_credit_cost`, `pricing`, `province_name`, `qq`, `redirect`, `redirect_to_shop`, `register`, `share`, `shopping`, `show`, `submit_certification_count`, `total_play`, `valid_play`, `valid_play_cost`, `valid_play_rate`, `view`, `vote`, `wechat`, `wechat_first_pay_cost`, `wechat_first_pay_count`, `wechat_first_pay_rate`, `wechat_login_cost`, `wechat_login_count`, `wechat_pay_amount`, `wifi_play`, `wifi_play_rate`, `gmt_create`, `gmt_modified`) VALUES ( 11111111111, '2021-06-16 00:00:00.000000', '2021-06-16', NULL, 0, 0.0000, 0.0000, 0.0000, 0.0000, 0.0000, 0.0000, NULL, 0, 0, 0, 0, 0, NULL, 0, NULL, 2, 183.2500, 0, 0.0000, NULL, NULL, 0, 0.0000, 0.0000, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 4.0800, 17.4500, 227.4900, 0, 0, NULL, 21, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 2, NULL, 183.2500, 9.5200, 366.4900, 0, 0, NULL, 1.3000, 0, 0.0000, 0.0000, 0, 0, 0.0000, 0.0000, 0, 0.0000, 0.0000, 0, 0, 0, 2, 0, 0.0000, 0.0000, 0, 0, NULL, 6, 0, 0, NULL, 0, 0, 0, 0, 0, 0, 0.0000, 0.0000, NULL, NULL, 1, 0, 0, 0, 0.0000, 0.0000, 0, 0.0000, 0.0000, 0, 0, 0, 0, 0, 0, 0.0000, 0, 0, 0.0000, 0, 0, 0, 0.0000, 0.0000, 0, 0, 0, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, 5860053, 2.5100, 0, 0, 0, 0.0000, NULL, NULL, 0, 0, 0, 0, 0, 0, 1611, 0, 1437, 157, 2.3300, 9.7500, 0, 0, 0, 0.0000, 0, 0.0000, 0.0000, 0, 0.0000, 994, 69.1700, NULL, NULL);\n");
        //statement.executeUpdate("update jl_advertiser_daily_data set cost = 20000 where advertiser_id = 11111111111");
        statement.execute("SELECT cost, `click` , `show`, concat( `ctr`,'%') as ctr  ,concat(convert(ctr,decimal(12,2)),'%') ctr1 FROM `jl_advertiser_daily_data` where cost>0");
        System.out.println((System.currentTimeMillis()-start)/1000);

        ResultSet resultSet = statement.getResultSet();
        while (resultSet.next()) {
             //log.info("ss:{},{},{},{}", resultSet.getString("advertiserId"),resultSet.getString("cost1"),resultSet.getString("avgShowCost"), resultSet.getString("avgShowCost"));
            log.info("ss:{}", resultSet.getString("cost"));
        }
    }
}
